import findspark
findspark.init()

from pyspark.sql import SparkSession
import pandas as pd
import plotly.express as px
import plotly.io as pio
import numpy as np

np.random.seed(423548)

pio.renderers.default = "notebook"

# Initialize Spark Session
spark = SparkSession.builder.appName("LightcastData").getOrCreate()

# Load Data
df = spark.read.option("header", "true").option("inferSchema", "true").option("multiLine","true").option("escape", "\"").csv("./data/lightcast_job_postings.csv")


df.printSchema() # comment this line when rendering the submission
df.show(5)

Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
25/06/26 13:27:38 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
25/06/26 13:27:40 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
[Stage 1:>                                                          (0 + 1) / 1]                                                                                

root
 |-- ID: string (nullable = true)
 |-- LAST_UPDATED_DATE: string (nullable = true)
 |-- LAST_UPDATED_TIMESTAMP: timestamp (nullable = true)
 |-- DUPLICATES: integer (nullable = true)
 |-- POSTED: string (nullable = true)
 |-- EXPIRED: string (nullable = true)
 |-- DURATION: integer (nullable = true)
 |-- SOURCE_TYPES: string (nullable = true)
 |-- SOURCES: string (nullable = true)
 |-- URL: string (nullable = true)
 |-- ACTIVE_URLS: string (nullable = true)
 |-- ACTIVE_SOURCES_INFO: string (nullable = true)
 |-- TITLE_RAW: string (nullable = true)
 |-- BODY: string (nullable = true)
 |-- MODELED_EXPIRED: string (nullable = true)
 |-- MODELED_DURATION: integer (nullable = true)
 |-- COMPANY: integer (nullable = true)
 |-- COMPANY_NAME: string (nullable = true)
 |-- COMPANY_RAW: string (nullable = true)
 |-- COMPANY_IS_STAFFING: boolean (nullable = true)
 |-- EDUCATION_LEVELS: string (nullable = true)
 |-- EDUCATION_LEVELS_NAME: string (nullable = true)
 |-- MIN_EDULEVELS: integer (nullable = true)
 |-- MIN_EDULEVELS_NAME: string (nullable = true)
 |-- MAX_EDULEVELS: integer (nullable = true)
 |-- MAX_EDULEVELS_NAME: string (nullable = true)
 |-- EMPLOYMENT_TYPE: integer (nullable = true)
 |-- EMPLOYMENT_TYPE_NAME: string (nullable = true)
 |-- MIN_YEARS_EXPERIENCE: integer (nullable = true)
 |-- MAX_YEARS_EXPERIENCE: integer (nullable = true)
 |-- IS_INTERNSHIP: boolean (nullable = true)
 |-- SALARY: integer (nullable = true)
 |-- REMOTE_TYPE: integer (nullable = true)
 |-- REMOTE_TYPE_NAME: string (nullable = true)
 |-- ORIGINAL_PAY_PERIOD: string (nullable = true)
 |-- SALARY_TO: integer (nullable = true)
 |-- SALARY_FROM: integer (nullable = true)
 |-- LOCATION: string (nullable = true)
 |-- CITY: string (nullable = true)
 |-- CITY_NAME: string (nullable = true)
 |-- COUNTY: integer (nullable = true)
 |-- COUNTY_NAME: string (nullable = true)
 |-- MSA: integer (nullable = true)
 |-- MSA_NAME: string (nullable = true)
 |-- STATE: integer (nullable = true)
 |-- STATE_NAME: string (nullable = true)
 |-- COUNTY_OUTGOING: integer (nullable = true)
 |-- COUNTY_NAME_OUTGOING: string (nullable = true)
 |-- COUNTY_INCOMING: integer (nullable = true)
 |-- COUNTY_NAME_INCOMING: string (nullable = true)
 |-- MSA_OUTGOING: integer (nullable = true)
 |-- MSA_NAME_OUTGOING: string (nullable = true)
 |-- MSA_INCOMING: integer (nullable = true)
 |-- MSA_NAME_INCOMING: string (nullable = true)
 |-- NAICS2: integer (nullable = true)
 |-- NAICS2_NAME: string (nullable = true)
 |-- NAICS3: integer (nullable = true)
 |-- NAICS3_NAME: string (nullable = true)
 |-- NAICS4: integer (nullable = true)
 |-- NAICS4_NAME: string (nullable = true)
 |-- NAICS5: integer (nullable = true)
 |-- NAICS5_NAME: string (nullable = true)
 |-- NAICS6: integer (nullable = true)
 |-- NAICS6_NAME: string (nullable = true)
 |-- TITLE: string (nullable = true)
 |-- TITLE_NAME: string (nullable = true)
 |-- TITLE_CLEAN: string (nullable = true)
 |-- SKILLS: string (nullable = true)
 |-- SKILLS_NAME: string (nullable = true)
 |-- SPECIALIZED_SKILLS: string (nullable = true)
 |-- SPECIALIZED_SKILLS_NAME: string (nullable = true)
 |-- CERTIFICATIONS: string (nullable = true)
 |-- CERTIFICATIONS_NAME: string (nullable = true)
 |-- COMMON_SKILLS: string (nullable = true)
 |-- COMMON_SKILLS_NAME: string (nullable = true)
 |-- SOFTWARE_SKILLS: string (nullable = true)
 |-- SOFTWARE_SKILLS_NAME: string (nullable = true)
 |-- ONET: string (nullable = true)
 |-- ONET_NAME: string (nullable = true)
 |-- ONET_2019: string (nullable = true)
 |-- ONET_2019_NAME: string (nullable = true)
 |-- CIP6: string (nullable = true)
 |-- CIP6_NAME: string (nullable = true)
 |-- CIP4: string (nullable = true)
 |-- CIP4_NAME: string (nullable = true)
 |-- CIP2: string (nullable = true)
 |-- CIP2_NAME: string (nullable = true)
 |-- SOC_2021_2: string (nullable = true)
 |-- SOC_2021_2_NAME: string (nullable = true)
 |-- SOC_2021_3: string (nullable = true)
 |-- SOC_2021_3_NAME: string (nullable = true)
 |-- SOC_2021_4: string (nullable = true)
 |-- SOC_2021_4_NAME: string (nullable = true)
 |-- SOC_2021_5: string (nullable = true)
 |-- SOC_2021_5_NAME: string (nullable = true)
 |-- LOT_CAREER_AREA: integer (nullable = true)
 |-- LOT_CAREER_AREA_NAME: string (nullable = true)
 |-- LOT_OCCUPATION: integer (nullable = true)
 |-- LOT_OCCUPATION_NAME: string (nullable = true)
 |-- LOT_SPECIALIZED_OCCUPATION: integer (nullable = true)
 |-- LOT_SPECIALIZED_OCCUPATION_NAME: string (nullable = true)
 |-- LOT_OCCUPATION_GROUP: integer (nullable = true)
 |-- LOT_OCCUPATION_GROUP_NAME: string (nullable = true)
 |-- LOT_V6_SPECIALIZED_OCCUPATION: integer (nullable = true)
 |-- LOT_V6_SPECIALIZED_OCCUPATION_NAME: string (nullable = true)
 |-- LOT_V6_OCCUPATION: integer (nullable = true)
 |-- LOT_V6_OCCUPATION_NAME: string (nullable = true)
 |-- LOT_V6_OCCUPATION_GROUP: integer (nullable = true)
 |-- LOT_V6_OCCUPATION_GROUP_NAME: string (nullable = true)
 |-- LOT_V6_CAREER_AREA: integer (nullable = true)
 |-- LOT_V6_CAREER_AREA_NAME: string (nullable = true)
 |-- SOC_2: string (nullable = true)
 |-- SOC_2_NAME: string (nullable = true)
 |-- SOC_3: string (nullable = true)
 |-- SOC_3_NAME: string (nullable = true)
 |-- SOC_4: string (nullable = true)
 |-- SOC_4_NAME: string (nullable = true)
 |-- SOC_5: string (nullable = true)
 |-- SOC_5_NAME: string (nullable = true)
 |-- LIGHTCAST_SECTORS: string (nullable = true)
 |-- LIGHTCAST_SECTORS_NAME: string (nullable = true)
 |-- NAICS_2022_2: integer (nullable = true)
 |-- NAICS_2022_2_NAME: string (nullable = true)
 |-- NAICS_2022_3: integer (nullable = true)
 |-- NAICS_2022_3_NAME: string (nullable = true)
 |-- NAICS_2022_4: integer (nullable = true)
 |-- NAICS_2022_4_NAME: string (nullable = true)
 |-- NAICS_2022_5: integer (nullable = true)
 |-- NAICS_2022_5_NAME: string (nullable = true)
 |-- NAICS_2022_6: integer (nullable = true)
 |-- NAICS_2022_6_NAME: string (nullable = true)

25/06/26 13:27:53 WARN SparkStringUtils: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.
[Stage 2:>                                                          (0 + 1) / 1]                                                                                

+--------------------+-----------------+----------------------+----------+--------+---------+--------+--------------------+--------------------+--------------------+-----------+-------------------+--------------------+--------------------+---------------+----------------+--------+--------------------+-----------+-------------------+----------------+---------------------+-------------+-------------------+-------------+------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+-------------+------+--------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
|                  ID|LAST_UPDATED_DATE|LAST_UPDATED_TIMESTAMP|DUPLICATES|  POSTED|  EXPIRED|DURATION|        SOURCE_TYPES|             SOURCES|                 URL|ACTIVE_URLS|ACTIVE_SOURCES_INFO|           TITLE_RAW|                BODY|MODELED_EXPIRED|MODELED_DURATION| COMPANY|        COMPANY_NAME|COMPANY_RAW|COMPANY_IS_STAFFING|EDUCATION_LEVELS|EDUCATION_LEVELS_NAME|MIN_EDULEVELS| MIN_EDULEVELS_NAME|MAX_EDULEVELS|MAX_EDULEVELS_NAME|EMPLOYMENT_TYPE|EMPLOYMENT_TYPE_NAME|MIN_YEARS_EXPERIENCE|MAX_YEARS_EXPERIENCE|IS_INTERNSHIP|SALARY|REMOTE_TYPE|REMOTE_TYPE_NAME|ORIGINAL_PAY_PERIOD|SALARY_TO|SALARY_FROM|            LOCATION|                CITY|    CITY_NAME|COUNTY|   COUNTY_NAME|  MSA|            MSA_NAME|STATE|STATE_NAME|COUNTY_OUTGOING|COUNTY_NAME_OUTGOING|COUNTY_INCOMING|COUNTY_NAME_INCOMING|MSA_OUTGOING|   MSA_NAME_OUTGOING|MSA_INCOMING|   MSA_NAME_INCOMING|NAICS2|         NAICS2_NAME|NAICS3|         NAICS3_NAME|NAICS4|         NAICS4_NAME|NAICS5|         NAICS5_NAME|NAICS6|         NAICS6_NAME|             TITLE|         TITLE_NAME|         TITLE_CLEAN|              SKILLS|         SKILLS_NAME|  SPECIALIZED_SKILLS|SPECIALIZED_SKILLS_NAME|      CERTIFICATIONS| CERTIFICATIONS_NAME|       COMMON_SKILLS|  COMMON_SKILLS_NAME|     SOFTWARE_SKILLS|SOFTWARE_SKILLS_NAME|      ONET|           ONET_NAME| ONET_2019|      ONET_2019_NAME|                CIP6|           CIP6_NAME|                CIP4|           CIP4_NAME|                CIP2|           CIP2_NAME|SOC_2021_2|     SOC_2021_2_NAME|SOC_2021_3|     SOC_2021_3_NAME|SOC_2021_4|SOC_2021_4_NAME|SOC_2021_5|SOC_2021_5_NAME|LOT_CAREER_AREA|LOT_CAREER_AREA_NAME|LOT_OCCUPATION| LOT_OCCUPATION_NAME|LOT_SPECIALIZED_OCCUPATION|LOT_SPECIALIZED_OCCUPATION_NAME|LOT_OCCUPATION_GROUP|LOT_OCCUPATION_GROUP_NAME|LOT_V6_SPECIALIZED_OCCUPATION|LOT_V6_SPECIALIZED_OCCUPATION_NAME|LOT_V6_OCCUPATION|LOT_V6_OCCUPATION_NAME|LOT_V6_OCCUPATION_GROUP|LOT_V6_OCCUPATION_GROUP_NAME|LOT_V6_CAREER_AREA|LOT_V6_CAREER_AREA_NAME|  SOC_2|          SOC_2_NAME|  SOC_3|          SOC_3_NAME|  SOC_4|     SOC_4_NAME|  SOC_5|     SOC_5_NAME|LIGHTCAST_SECTORS|LIGHTCAST_SECTORS_NAME|NAICS_2022_2|   NAICS_2022_2_NAME|NAICS_2022_3|   NAICS_2022_3_NAME|NAICS_2022_4|   NAICS_2022_4_NAME|NAICS_2022_5|   NAICS_2022_5_NAME|NAICS_2022_6|   NAICS_2022_6_NAME|
+--------------------+-----------------+----------------------+----------+--------+---------+--------+--------------------+--------------------+--------------------+-----------+-------------------+--------------------+--------------------+---------------+----------------+--------+--------------------+-----------+-------------------+----------------+---------------------+-------------+-------------------+-------------+------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+-------------+------+--------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
|1f57d95acf4dc67ed...|         9/6/2024|  2024-09-06 20:32:...|         0|6/2/2024| 6/8/2024|       6|   [\n  "Company"\n]|[\n  "brassring.c...|[\n  "https://sjo...|         []|               NULL|Enterprise Analys...|31-May-2024\n\nEn...|       6/8/2024|               6|  894731|          Murphy USA| Murphy USA|              false|       [\n  2\n]| [\n  "Bachelor's ...|            2|  Bachelor's degree|         NULL|              NULL|              1|Full-time (> 32 h...|                   2|                   2|        false|  NULL|          0|          [None]|               NULL|     NULL|       NULL|{\n  "lat": 33.20...|RWwgRG9yYWRvLCBBUg==|El Dorado, AR|  5139|     Union, AR|20980|       El Dorado, AR|    5|  Arkansas|           5139|           Union, AR|           5139|           Union, AR|       20980|       El Dorado, AR|       20980|       El Dorado, AR|    44|        Retail Trade|   441|Motor Vehicle and...|  4413|Automotive Parts,...| 44133|Automotive Parts ...|441330|Automotive Parts ...|ET29C073C03D1F86B4|Enterprise Analysts|enterprise analys...|[\n  "KS126DB6T06...|[\n  "Merchandisi...|[\n  "KS126DB6T06...|   [\n  "Merchandisi...|                  []|                  []|[\n  "KS126706DPF...|[\n  "Mathematics...|[\n  "KS440W865GC...|[\n  "SQL (Progra...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|[\n  "45.0601",\n...|[\n  "Economics, ...|[\n  "45.06",\n  ...|[\n  "Economics",...|[\n  "45",\n  "27...|[\n  "Social Scie...|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231010|Business Intellig...|                  23101011|           General ERP Analy...|                2310|     Business Intellig...|                     23101011|              General ERP Analy...|           231010|  Business Intellig...|                   2310|        Business Intellig...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|        [\n  7\n]|  [\n  "Artificial ...|          44|        Retail Trade|         441|Motor Vehicle and...|        4413|Automotive Parts,...|       44133|Automotive Parts ...|      441330|Automotive Parts ...|
|0cb072af26757b6c4...|         8/2/2024|  2024-08-02 17:08:...|         0|6/2/2024| 8/1/2024|    NULL| [\n  "Job Board"\n]| [\n  "maine.gov"\n]|[\n  "https://job...|         []|               NULL|Oracle Consultant...|Oracle Consultant...|       8/1/2024|            NULL|  133098|Smx Corporation L...|        SMX|               true|      [\n  99\n]| [\n  "No Educatio...|           99|No Education Listed|         NULL|              NULL|              1|Full-time (> 32 h...|                   3|                   3|        false|  NULL|          1|          Remote|               NULL|     NULL|       NULL|{\n  "lat": 44.31...|    QXVndXN0YSwgTUU=|  Augusta, ME| 23011|  Kennebec, ME|12300|Augusta-Watervill...|   23|     Maine|          23011|        Kennebec, ME|          23011|        Kennebec, ME|       12300|Augusta-Watervill...|       12300|Augusta-Watervill...|    56|Administrative an...|   561|Administrative an...|  5613| Employment Services| 56132|Temporary Help Se...|561320|Temporary Help Se...|ET21DDA63780A7DC09| Oracle Consultants|oracle consultant...|[\n  "KS122626T55...|[\n  "Procurement...|[\n  "KS122626T55...|   [\n  "Procurement...|                  []|                  []|                  []|                  []|[\n  "BGSBF3F508F...|[\n  "Oracle Busi...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|                  []|                  []|                  []|                  []|                  []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231010|Business Intellig...|                  23101012|           Oracle Consultant...|                2310|     Business Intellig...|                     23101012|              Oracle Consultant...|           231010|  Business Intellig...|                   2310|        Business Intellig...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          56|Administrative an...|         561|Administrative an...|        5613| Employment Services|       56132|Temporary Help Se...|      561320|Temporary Help Se...|
|85318b12b3331fa49...|         9/6/2024|  2024-09-06 20:32:...|         1|6/2/2024| 7/7/2024|      35| [\n  "Job Board"\n]|[\n  "dejobs.org"\n]|[\n  "https://dej...|         []|               NULL|        Data Analyst|Taking care of pe...|      6/10/2024|               8|39063746|            Sedgwick|   Sedgwick|              false|       [\n  2\n]| [\n  "Bachelor's ...|            2|  Bachelor's degree|         NULL|              NULL|              1|Full-time (> 32 h...|                   5|                NULL|        false|  NULL|          0|          [None]|               NULL|     NULL|       NULL|{\n  "lat": 32.77...|    RGFsbGFzLCBUWA==|   Dallas, TX| 48113|    Dallas, TX|19100|Dallas-Fort Worth...|   48|     Texas|          48113|          Dallas, TX|          48113|          Dallas, TX|       19100|Dallas-Fort Worth...|       19100|Dallas-Fort Worth...|    52|Finance and Insur...|   524|Insurance Carrier...|  5242|Agencies, Brokera...| 52429|Other Insurance R...|524291|    Claims Adjusting|ET3037E0C947A02404|      Data Analysts|        data analyst|[\n  "KS1218W78FG...|[\n  "Management"...|[\n  "ESF3939CE1F...|   [\n  "Exception R...|[\n  "KS683TN76T7...|[\n  "Security Cl...|[\n  "KS1218W78FG...|[\n  "Management"...|[\n  "KS126HY6YLT...|[\n  "Microsoft O...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|                  []|                  []|                  []|                  []|                  []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231113|Data / Data Minin...|                  23111310|                   Data Analyst|                2311|     Data Analysis and...|                     23111310|                      Data Analyst|           231113|  Data / Data Minin...|                   2311|        Data Analysis and...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          52|Finance and Insur...|         524|Insurance Carrier...|        5242|Agencies, Brokera...|       52429|Other Insurance R...|      524291|    Claims Adjusting|
|1b5c3941e54a1889e...|         9/6/2024|  2024-09-06 20:32:...|         1|6/2/2024|7/20/2024|      48| [\n  "Job Board"\n]|[\n  "disabledper...|[\n  "https://www...|         []|               NULL|Sr. Lead Data Mgm...|About this role:\...|      6/12/2024|              10|37615159|         Wells Fargo|Wells Fargo|              false|      [\n  99\n]| [\n  "No Educatio...|           99|No Education Listed|         NULL|              NULL|              1|Full-time (> 32 h...|                   3|                NULL|        false|  NULL|          0|          [None]|               NULL|     NULL|       NULL|{\n  "lat": 33.44...|    UGhvZW5peCwgQVo=|  Phoenix, AZ|  4013|  Maricopa, AZ|38060|Phoenix-Mesa-Chan...|    4|   Arizona|           4013|        Maricopa, AZ|           4013|        Maricopa, AZ|       38060|Phoenix-Mesa-Chan...|       38060|Phoenix-Mesa-Chan...|    52|Finance and Insur...|   522|Credit Intermedia...|  5221|Depository Credit...| 52211|  Commercial Banking|522110|  Commercial Banking|ET2114E0404BA30075|Management Analysts|sr lead data mgmt...|[\n  "KS123QX62QY...|[\n  "Exit Strate...|[\n  "KS123QX62QY...|   [\n  "Exit Strate...|                  []|                  []|[\n  "KS7G6NP6R6L...|[\n  "Reliability...|[\n  "KS4409D76NW...|[\n  "SAS (Softwa...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|                  []|                  []|                  []|                  []|                  []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231113|Data / Data Minin...|                  23111310|                   Data Analyst|                2311|     Data Analysis and...|                     23111310|                      Data Analyst|           231113|  Data / Data Minin...|                   2311|        Data Analysis and...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|        [\n  6\n]|  [\n  "Data Privac...|          52|Finance and Insur...|         522|Credit Intermedia...|        5221|Depository Credit...|       52211|  Commercial Banking|      522110|  Commercial Banking|
|cb5ca25f02bdf25c1...|        6/19/2024|   2024-06-19 07:00:00|         0|6/2/2024|6/17/2024|      15|[\n  "FreeJobBoar...|[\n  "craigslist....|[\n  "https://mod...|         []|               NULL|Comisiones de $10...|Comisiones de $10...|      6/17/2024|              15|       0|        Unclassified|      LH/GM|              false|      [\n  99\n]| [\n  "No Educatio...|           99|No Education Listed|         NULL|              NULL|              3|Part-time / full-...|                NULL|                NULL|        false| 92500|          0|          [None]|               year|   150000|      35000|{\n  "lat": 37.63...|    TW9kZXN0bywgQ0E=|  Modesto, CA|  6099|Stanislaus, CA|33700|         Modesto, CA|    6|California|           6099|      Stanislaus, CA|           6099|      Stanislaus, CA|       33700|         Modesto, CA|       33700|         Modesto, CA|    99|Unclassified Indu...|   999|Unclassified Indu...|  9999|Unclassified Indu...| 99999|Unclassified Indu...|999999|Unclassified Indu...|ET0000000000000000|       Unclassified|comisiones de por...|                  []|                  []|                  []|                     []|                  []|                  []|                  []|                  []|                  []|                  []|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|                  []|                  []|                  []|                  []|                  []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231010|Business Intellig...|                  23101012|           Oracle Consultant...|                2310|     Business Intellig...|                     23101012|              Oracle Consultant...|           231010|  Business Intellig...|                   2310|        Business Intellig...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          99|Unclassified Indu...|         999|Unclassified Indu...|        9999|Unclassified Indu...|       99999|Unclassified Indu...|      999999|Unclassified Indu...|
+--------------------+-----------------+----------------------+----------+--------+---------+--------+--------------------+--------------------+--------------------+-----------+-------------------+--------------------+--------------------+---------------+----------------+--------+--------------------+-----------+-------------------+----------------+---------------------+-------------+-------------------+-------------+------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+-------------+------+--------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
only showing top 5 rows

# clean the data

from pyspark.ml.feature import StringIndexer, OneHotEncoder, VectorAssembler
from pyspark.ml import Pipeline
from pyspark.sql.functions import when, col

df= df.drop("ID" ,"LAST_UPDATED_DATE","LAST_UPDATED_TIMESTAMP","DUPLICATES", "EXPIRED","SOURCE_TYPES","SOURCES",
            "URL", "ACTIVE_URLS", "ACTIVE_SOURCES_INFO","TITLE_RAW","BODY", "MODELED_EXPIRED", "MODELED_DURATION", "COMPANY", 
            "COMPANY_NAME", "COMPANY_RAW", "COMPANY_IS_STAFFING", "EDUCATION_LEVELS")

df = df.withColumn("REMOTE_TYPE_NAME", 
                   when(col("REMOTE_TYPE_NAME") == "[None]", "Not Remote")
                   .otherwise(col("REMOTE_TYPE_NAME")))

df_clean = df.na.drop(subset=[
    "salary", "MIN_YEARS_EXPERIENCE", "MAX_YEARS_EXPERIENCE",
    "EDUCATION_LEVELS_NAME", "EMPLOYMENT_TYPE_NAME", "REMOTE_TYPE_NAME",
    "DURATION",
])

df_clean.show(5)

+---------+--------+---------------------+-------------+------------------+-------------+--------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+------------------+------+--------------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------+-------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+-----------------+--------------------+---------------+--------------------+------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
|   POSTED|DURATION|EDUCATION_LEVELS_NAME|MIN_EDULEVELS|MIN_EDULEVELS_NAME|MAX_EDULEVELS|  MAX_EDULEVELS_NAME|EMPLOYMENT_TYPE|EMPLOYMENT_TYPE_NAME|MIN_YEARS_EXPERIENCE|MAX_YEARS_EXPERIENCE|IS_INTERNSHIP|SALARY|REMOTE_TYPE|REMOTE_TYPE_NAME|ORIGINAL_PAY_PERIOD|SALARY_TO|SALARY_FROM|            LOCATION|                CITY|         CITY_NAME|COUNTY|         COUNTY_NAME|  MSA|            MSA_NAME|STATE|STATE_NAME|COUNTY_OUTGOING|COUNTY_NAME_OUTGOING|COUNTY_INCOMING|COUNTY_NAME_INCOMING|MSA_OUTGOING|   MSA_NAME_OUTGOING|MSA_INCOMING|   MSA_NAME_INCOMING|NAICS2|         NAICS2_NAME|NAICS3|         NAICS3_NAME|NAICS4|         NAICS4_NAME|NAICS5|         NAICS5_NAME|NAICS6|         NAICS6_NAME|             TITLE|          TITLE_NAME|         TITLE_CLEAN|              SKILLS|         SKILLS_NAME|  SPECIALIZED_SKILLS|SPECIALIZED_SKILLS_NAME|CERTIFICATIONS|CERTIFICATIONS_NAME|       COMMON_SKILLS|  COMMON_SKILLS_NAME|     SOFTWARE_SKILLS|SOFTWARE_SKILLS_NAME|      ONET|           ONET_NAME| ONET_2019|      ONET_2019_NAME|             CIP6|           CIP6_NAME|           CIP4|           CIP4_NAME|        CIP2|           CIP2_NAME|SOC_2021_2|     SOC_2021_2_NAME|SOC_2021_3|     SOC_2021_3_NAME|SOC_2021_4|SOC_2021_4_NAME|SOC_2021_5|SOC_2021_5_NAME|LOT_CAREER_AREA|LOT_CAREER_AREA_NAME|LOT_OCCUPATION| LOT_OCCUPATION_NAME|LOT_SPECIALIZED_OCCUPATION|LOT_SPECIALIZED_OCCUPATION_NAME|LOT_OCCUPATION_GROUP|LOT_OCCUPATION_GROUP_NAME|LOT_V6_SPECIALIZED_OCCUPATION|LOT_V6_SPECIALIZED_OCCUPATION_NAME|LOT_V6_OCCUPATION|LOT_V6_OCCUPATION_NAME|LOT_V6_OCCUPATION_GROUP|LOT_V6_OCCUPATION_GROUP_NAME|LOT_V6_CAREER_AREA|LOT_V6_CAREER_AREA_NAME|  SOC_2|          SOC_2_NAME|  SOC_3|          SOC_3_NAME|  SOC_4|     SOC_4_NAME|  SOC_5|     SOC_5_NAME|LIGHTCAST_SECTORS|LIGHTCAST_SECTORS_NAME|NAICS_2022_2|   NAICS_2022_2_NAME|NAICS_2022_3|   NAICS_2022_3_NAME|NAICS_2022_4|   NAICS_2022_4_NAME|NAICS_2022_5|   NAICS_2022_5_NAME|NAICS_2022_6|   NAICS_2022_6_NAME|
+---------+--------+---------------------+-------------+------------------+-------------+--------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+------------------+------+--------------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------+-------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+-----------------+--------------------+---------------+--------------------+------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
|5/25/2024|      11| [\n  "Bachelor's ...|            2| Bachelor's degree|         NULL|                NULL|              1|Full-time (> 32 h...|                   2|                   2|        false|131100|          0|      Not Remote|               year|   148800|     113400|{\n  "lat": 33.44...|    UGhvZW5peCwgQVo=|       Phoenix, AZ|  4013|        Maricopa, AZ|38060|Phoenix-Mesa-Chan...|    4|   Arizona|           4013|        Maricopa, AZ|           4013|        Maricopa, AZ|       38060|Phoenix-Mesa-Chan...|       38060|Phoenix-Mesa-Chan...|    54|Professional, Sci...|   541|Professional, Sci...|  5413|Architectural, En...| 54133|Engineering Services|541330|Engineering Services|ET9C3F621F0C8921B8|  Permit Specialists|caltrans permit s...|[\n  "KS4407N6CMT...|[\n  "Salesforce"...|[\n  "KS4407N6CMT...|   [\n  "Salesforce"...|            []|                 []|[\n  "KS127D361PF...|[\n  "Operations"...|[\n  "KS4407N6CMT...|[\n  "Salesforce"...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|               []|                  []|             []|                  []|          []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231510|Computer Systems ...|                  23151012|           Enterprise Architect|                2315|     Network and Syste...|                     23151012|              Enterprise Architect|           231510|  Computer Systems ...|                   2315|        Network and Syste...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          54|Professional, Sci...|         541|Professional, Sci...|        5413|Architectural, En...|       54133|Engineering Services|      541330|Engineering Services|
|5/25/2024|      28| [\n  "Bachelor's ...|            2| Bachelor's degree|            4|Ph.D. or professi...|              1|Full-time (> 32 h...|                   3|                   3|        false|136950|          1|          Remote|               year|   158600|     115300|{\n  "lat": 0,\n ...|W1Vua25vd24gQ2l0e...|[Unknown City], MN| 27999|[Unknown county], MN| NULL|                NULL|   27| Minnesota|          27999|[Unknown county], MN|          27999|[Unknown county], MN|        NULL|                NULL|        NULL|                NULL|    52|Finance and Insur...|   524|Insurance Carrier...|  5241|  Insurance Carriers| 52411|Direct Life, Heal...|524114|Direct Health and...|ET5AA72D0E18D0EFE5|Enterprise Archit...|senior enterprise...|[\n  "KS44241613X...|[\n  "Wireless Co...|[\n  "KS124X67987...|   [\n  "Infrastruct...|            []|                 []|[\n  "KS44241613X...|[\n  "Wireless Co...|[\n  "KS124X67987...|[\n  "Infrastruct...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|[\n  "11.0701"\n]|[\n  "Computer Sc...|[\n  "11.07"\n]|[\n  "Computer Sc...|[\n  "11"\n]|[\n  "Computer an...|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231510|Computer Systems ...|                  23151012|           Enterprise Architect|                2315|     Network and Syste...|                     23151012|              Enterprise Architect|           231510|  Computer Systems ...|                   2315|        Network and Syste...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          52|Finance and Insur...|         524|Insurance Carrier...|        5241|  Insurance Carriers|       52411|Direct Life, Heal...|      524114|Direct Health and...|
|5/25/2024|      28| [\n  "Bachelor's ...|            2| Bachelor's degree|            4|Ph.D. or professi...|              1|Full-time (> 32 h...|                   3|                   3|        false|136950|          1|          Remote|               year|   158600|     115300|{\n  "lat": 0,\n ...|W1Vua25vd24gQ2l0e...|[Unknown City], GA| 13999|[Unknown county], GA| NULL|                NULL|   13|   Georgia|          13999|[Unknown county], GA|          13999|[Unknown county], GA|        NULL|                NULL|        NULL|                NULL|    52|Finance and Insur...|   524|Insurance Carrier...|  5241|  Insurance Carriers| 52411|Direct Life, Heal...|524114|Direct Health and...|ET5AA72D0E18D0EFE5|Enterprise Archit...|senior enterprise...|[\n  "KS44241613X...|[\n  "Wireless Co...|[\n  "KS124X67987...|   [\n  "Infrastruct...|            []|                 []|[\n  "KS44241613X...|[\n  "Wireless Co...|[\n  "KS124X67987...|[\n  "Infrastruct...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|[\n  "11.0701"\n]|[\n  "Computer Sc...|[\n  "11.07"\n]|[\n  "Computer Sc...|[\n  "11"\n]|[\n  "Computer an...|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231510|Computer Systems ...|                  23151012|           Enterprise Architect|                2315|     Network and Syste...|                     23151012|              Enterprise Architect|           231510|  Computer Systems ...|                   2315|        Network and Syste...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          52|Finance and Insur...|         524|Insurance Carrier...|        5241|  Insurance Carriers|       52411|Direct Life, Heal...|      524114|Direct Health and...|
| 5/3/2024|       8| [\n  "Bachelor's ...|            2| Bachelor's degree|         NULL|                NULL|              1|Full-time (> 32 h...|                   3|                   3|        false|104000|          0|      Not Remote|               hour|   104000|     104000|{\n  "lat": 29.76...|    SG91c3RvbiwgVFg=|       Houston, TX| 48201|          Harris, TX|26420|Houston-The Woodl...|   48|     Texas|          48201|          Harris, TX|          48201|          Harris, TX|       26420|Houston-The Woodl...|       26420|Houston-Pasadena-...|    52|Finance and Insur...|   524|Insurance Carrier...|  5242|Agencies, Brokera...| 52421|Insurance Agencie...|524210|Insurance Agencie...|ETDBB7C33FFAA14374|Power Business In...|    power bi analyst|[\n  "KS13USA80NE...|[\n  "Power BI",\...|[\n  "KS13USA80NE...|   [\n  "Power BI",\...|            []|                 []|                  []|                  []|[\n  "KS13USA80NE...|[\n  "Power BI",\...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|               []|                  []|             []|                  []|          []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             23|Information Techn...|        231010|Business Intellig...|                  23101010|           Business Intellig...|                2310|     Business Intellig...|                     23101010|              Business Intellig...|           231010|  Business Intellig...|                   2310|        Business Intellig...|                23|   Information Techn...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          52|Finance and Insur...|         524|Insurance Carrier...|        5242|Agencies, Brokera...|       52421|Insurance Agencie...|      524210|Insurance Agencie...|
|7/31/2024|      37| [\n  "Bachelor's ...|            2| Bachelor's degree|         NULL|                NULL|              1|Full-time (> 32 h...|                   3|                   3|        false| 80000|          0|      Not Remote|               year|   100000|      60000|{\n  "lat": 35.46...|T2tsYWhvbWEgQ2l0e...| Oklahoma City, OK| 40109|        Oklahoma, OK|36420|   Oklahoma City, OK|   40|  Oklahoma|          40109|        Oklahoma, OK|          40109|        Oklahoma, OK|       36420|   Oklahoma City, OK|       36420|   Oklahoma City, OK|    61|Educational Services|   611|Educational Services|  6116|Other Schools and...| 61169|All Other Schools...|611691|Exam Preparation ...|ET30073907A7DEEDFC|Quantitative Anal...|senior scm analys...|[\n  "KS1241P6PXR...|[\n  "Forecasting...|[\n  "ES73A557482...|   [\n  "Inventory M...|            []|                 []|[\n  "KS1241P6PXR...|[\n  "Forecasting...|[\n  "KS120QQ6ZN0...|[\n  "JIRA",\n  "...|15-2051.01|Business Intellig...|15-2051.01|Business Intellig...|               []|                  []|             []|                  []|          []|                  []|   15-0000|Computer and Math...|   15-2000|Mathematical Scie...|   15-2050|Data Scientists|   15-2051|Data Scientists|             11|Business Manageme...|        111110|Business / Manage...|                  11111012|           Business Analyst ...|                1111|        Business Analysis|                     11111012|              Business Analyst ...|           111110|  Business / Manage...|                   1111|           Business Analysis|                11|   Business Manageme...|15-0000|Computer and Math...|15-2000|Mathematical Scie...|15-2050|Data Scientists|15-2051|Data Scientists|             NULL|                  NULL|          61|Educational Services|         611|Educational Services|        6116|Other Schools and...|       61169|All Other Schools...|      611691|Exam Preparation ...|
+---------+--------+---------------------+-------------+------------------+-------------+--------------------+---------------+--------------------+--------------------+--------------------+-------------+------+-----------+----------------+-------------------+---------+-----------+--------------------+--------------------+------------------+------+--------------------+-----+--------------------+-----+----------+---------------+--------------------+---------------+--------------------+------------+--------------------+------------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------+--------------------+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+-----------------------+--------------+-------------------+--------------------+--------------------+--------------------+--------------------+----------+--------------------+----------+--------------------+-----------------+--------------------+---------------+--------------------+------------+--------------------+----------+--------------------+----------+--------------------+----------+---------------+----------+---------------+---------------+--------------------+--------------+--------------------+--------------------------+-------------------------------+--------------------+-------------------------+-----------------------------+----------------------------------+-----------------+----------------------+-----------------------+----------------------------+------------------+-----------------------+-------+--------------------+-------+--------------------+-------+---------------+-------+---------------+-----------------+----------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+------------+--------------------+
only showing top 5 rows

titles = df_clean.select("TITLE_NAME").distinct().rdd.flatMap(lambda x: x).collect()
for title in titles:
    print(title)

[Stage 4:>                                                          (0 + 1) / 1][Stage 6:>                                                          (0 + 1) / 1]

Trust Officers
Cloud Migration Engineers
EDI Developers
Permit Specialists
Data Integration Leads
Blockchain Developers
Lead Intelligence Analysts
Sales Architects
Business Intelligence Leads
Data Analyst Managers
Project Support Analysts
Sales Planning Analysts
Hourly Managers
Industrial Equipment Mechanics
Value Engineers
Enterprise Applications Consultants
SAP FICO Consultants
SAP ABAP Consultants
Oracle Erp Consultants
Member Liaisons
Data Solutions Analysts
Transformation Analysts
GIS Data Analysts
Japanese Bilingual Administrative Assistants
Project Leads
People Analytics Analysts
Human Resources Reporting Analysts
Implementation Consultants
Lead Enterprise Architects
Branch Bankers
Growth Marketing Analysts
Chargeback Analysts
Strategy Leads
Innovation Analysts
Business Insights Managers
CRM Business Analysts
Localization Producers
EDI Analysts
Scientific Data Analysts
Bilingual Japanese Customer Service Representatives
SQL/ETL Developers
Data Quality Leads
Data Visualization Analysts
Data Analytics Engineers
Foundation Administrators
SQL Reporting Analysts
Procurement Analysts
Manual Testers
Analytics Associates
Supply Chain Architects
SAP SD Analysts
Oracle Cloud Financials Consultants
Data Quality Assurance Analysts
Client Finance Directors
Population Health Analysts
Enterprise Solutions Consultants
Digital Product Analysts
Line Pilots
Processing Clerks
Client Solutions Strategists
Business Intelligence Specialists
Accounting Consultants
Business Intelligence Analysts
SAP CRM Consultants
Human Capital Management Consultants
IAM Architects
SAP Ariba Consultants
Territory Assistants
Immigration Analysts
Customer Experience Associates
Global Analysts
Analysts
Document Management Clerks
Data Reporting Analysts
Quality Analysts
SAP HANA Consultants
Site Analysts
Business Architects
Data Analytics Developers
Digital Solution Architect Managers
Information Governance Analysts
Business Coaches
Configuration Management Analysts
Commercial Analysts
Analytics Consultants
Buyers
Enterprise Solutions Architects
Remediation Analysts
Search Planners
Insurance Associates
People Operations Generalists
Appeals Specialists
IT Project Assistants
Data Analytics Architects
Methods Analysts
Liquidity Analysts
Functional Consultants
SAP HR Consultants
Performance Directors
ERP Implementation Specialists
Tribal Administrators
Study Design Leads
Equipment Analysts
Quality Assurance Monitors
Integration Team Leads
Invoice Analysts
SAP FICO Functional Consultants
Enterprise Relationship Managers
Enterprise Data Architects
SAP PP/QM Consultants
Management Analysts
Speech Assistants
SAP Business Analysts
Data Migration Analysts
SAP Techno Functional Consultants
SAP Supply Chain Consultants
Enterprise Directors
Data Processing Analysts
SAS Data Analysts
Manufacturing Consultants
Demand Analysts
Netsuite Principal Consultants
OTM Consultants
Modernization Superintendents
Mapping Analysts
Enterprise Systems Architects
Business Intelligence Associates
Oracle Business Analysts
SAP Support Analysts
Automation Engineers
Excel VBA Developers
Financial Data Analysts
Power Analysts
Sales Analysts
Lead IT Analysts
Development Analysts
Analytics Managers
Financial Applications Specialists
Sales Solution Architects
Survey Analysts
Data Analysts/Data Scientists
Quality Control Reviewers
Oracle EBS Consultants
Data Services Analysts
ERP Implementation Managers
Knowledge Analysts
Enterprise Business Analysts
Test Data Analysts
Techno Functional Analysts
Netsuite Consultants
Cryptologists
PMO Analysts
Reference Data Analysts
Clinical Data Analysts
Enterprise Business Architects
Enterprise Cloud Architects
SAP Security Consultants
Data Leads
Tools Developers
Marketing Analysts
Financial Planning and Analysis Analysts
Finance Systems Analysts
Production Operators
Oracle Functional Analysts
IT Buyers
Process Engineers
Privacy Analysts
Enterprise Resources Planning Managers
Real Estate Analysts
Systems Integration Architects
End User Computing Analysts
Data Analysts/Developers
Publishing Specialists
SQL Analysts
Account Analysts
Engineering Data Analysts
Oracle EBS Business Analysts
Directors of Business Intelligence
Reporting Associates
SAP HCM Consultants
Feasibility Managers
Data Management Administrators
Walkers
Production Analysts
HRIS Associates
Data Analytics Leads
Data Analytics Specialists
Data Security Analysts
Principal Data Scientists
Researchers
Procurement Business Analysts
Oracle Applications Analysts
Forecast Analysts
Supply Chain Data Analysts
Analytics and Insights Managers
MDM Developers
Business Support Analysts
Food and Beverage Analysts
Intelligence Research Analysts
Validation Leads
Associate Business Managers
Enterprise Data Analysts
IT Governance Analysts
Domain Architects
Compliance Business Analysts
Implementation Specialists
Placement Managers
Corporate Architects
Splunk Developers
Work Force Management Analysts
Banking Consultants
Data Stewards
SAP MDM Consultants
SAP Specialists
Data Validation Analysts
Business Intelligence Data Warehouse Architects
Data Science Associates
Solution Leads
SAP Data Analysts
SAP Finance Consultants
Doctors
SQL Data Analysts
Patient Revenue Cycle Specialists
People Analytics Managers
Data Scientists
Digital Data Analysts
Data Control Clerks
Storeroom Clerks
Finance Business Analysts
SAP HR Analysts
Business Intelligence and Analytics Managers
Brand Activation Managers
Enterprise Project Managers
Data Analytics Consultants
Programmer Analysts
Sales Data Analysts
Data Reviewers
Contract Analysts
Decision Support Analysts
Data Associates
E-Commerce Architects
Risk Control Managers
Debt Specialists
Risk and Controls Managers
Data Entry Analysts
Platform Analysts
Financial Systems Analysts
Claims Resolution Analysts
Lead Business Intelligence Analysts
Inside Auto Claims Representatives
Customer Contact Center Managers
Data Governance Analysts
Business Operations Specialists
SAP Technical Consultants
Data Engineering Managers
SAP EWM Consultants
Tax Controllers
Transmission Analysts
Business Analysts
Credit Analysts
CSV Consultants
Patient Services Associates
Research Data Analysts
Medical Economics Analysts
SQL Administrators
SAP Master Data Analysts
Customer Care Analysts
Title Processors
Power Business Intelligence Developers
Data Quality Analysts
Quality Assurance Analysts
Consulting Engagement Managers
Vice Presidents of Operational Excellence
Insights Analysts
Inventory Analysts
Data Support Analysts
SAP Business One Consultants
Data Management Leads
Platform Administrators
Oracle Financial Functional Consultants
Architecture Managers
ERP Analysts
Growth Associates
Directors of School Nutrition
Operations and Policy Analysts
Lead Data Analysts
SAP SD Functional Consultants
Assistant Federal Security Directors
Oracle Cloud Architects
Quality Assurance Leads
SAP FICO Analysts
Repair Analysts
SAP SD Consultants
SAP SRM Consultants
Hyperion Consultants
SAP Systems Analysts
Adobe Experience Manager Architects
Quality Assurance Analysts/Engineers
Oracle Utilities Professional Services CC&B Implementation Consultants
SAP OTC Consultants
Data Warehouse Business Analysts
Client Data Analysts
Documentum Administrators
Project Analysts
Korean Linguists
Data Governance Managers
Oracle Consultants
Navigators
Customer Data Analysts
Enterprise Technical Architects
Cash Management Associates
Equity Traders
Financial Analysts
ERP Architects
Business Intelligence and Data Analysts
Automation Consultants
Governance Analysts
ERP Systems Analysts
Commercial Banking Credit Analysts
Certified Public Accountants
Data Operations Analysts
Category Analysts
Regional Business Managers
GCP Auditors
Architectural Project Coordinators
Delivery Analysts
SAP MM Functional Consultants
Techno Functional Leads
Clinical Data Managers
Administrators
Enterprise Network Managers
Healthcare Business Analysts
Solutions Architects
Ward Clerks
Account Coordinators
Data Abstractors
Data Scientists/Analysts
SAP Functional Consultants
Product Lifecycle Managers
Data Intelligence Analysts
Data Warehouse Analysts
CNC Administrators
Life Sciences Consultants
Data Processing Clerks
Production Planning Analysts
Business Growth Consultants
Chief Architects
Logistics Administrators
Vendor Analysts
Quantitative Analytics Managers
Treasury Consultants
Crime Analysts
SAP Business Process Analysts
Call Center Analysts
GRC Analysts
Competitive Intelligence Analysts
Oracle Technical Consultants
Headend Engineers
Claims Business Analysts
General Managers
Systems Architecture Engineers
SAP Ariba Managers
Customer Analytics Managers
Customer Experience Analysts
LIS Analysts
SAP Consultants
SAP Hybris Consultants
SAP PI Consultants
Enterprise Application Analysts
Data and Analytics Consultants
Agile Product Owners
Business Intelligence Reporting Analysts
Management Associates
Health Data Analysts
Enterprise Systems Engineers
Lead Analysts
Data Migration Consultants
Professors of Biological Sciences
Oracle Business Systems Analysts
Regional Fleet Managers
Enterprise Systems Analysts
Inflight Supervisors
Operations Process Engineers
SAP Developers
Customer Care Experts
Reporting Analysts
Commercial Excellence Managers
Lead Quality Analysts
Oracle EBS Technical Consultants
Records Assistants
Directors of Cloud Security
.NET Technical Architects
Associates
Trend Analysts
Digital Analysts
Innovation Specialists
Data Governance Leads
Data Analysts
Directors of Sponsored Programs
Enterprise Resource Planning Managers
Wealth Management Analysts
Quantitative Data Analysts
Origination Associates
Enterprise Business Consultants
SAP Application Consultants
Information Analysts
Process Improvement Analysts
Consumer Analysts
Communications Analysts
GIS Consultants
Data Analyst Specialists
Plant Maintenance Managers
Netsuite Functional Consultants
Plant Chemists
Integration Consultants
Data Management Analysts
Oracle Financial Consultants
SAP Plant Maintenance Consultants
Analytics Leads
Lead Level Designers
Solutions Analysts
Data and Reporting Analysts
Data Analytics Product Managers
SAP Analysts
Implementation Analysts
Revenue Cycle Analysts
Case Management Associates
SAP Successfactors Consultants
IT Governance Managers
Sales Professionals
Principal Architects
Oracle HCM Consultants
Data Science Analysts
HANA Consultants
OSP Managers
Interface Analysts
Transportation Systems Analysts
Forms Analysts
Master Data Coordinators
Enterprise Application Architects
Epic Security Analysts
Intercompany Accountants
Client Insights Analysts
Data Analytics Associates
Unclassified
SAP SD/MM Consultants
SAP Administrators
Supply Chain Business Managers
Assistant Service Experience Managers
Data Clerks
Data Visualization Specialists
Growth Specialists
SAP Basis Leads
Functional Analysts
SAP Functional Analysts
Knowledge Experts
Artificial Intelligence Engineers
Financial Planning and Analysis Managers
Business Intelligence Data Analysts
Peoplesoft Business Analysts
Platform Architects
Systems Architects
Assessment Analysts
Solutions Consultants
Surface Designers
SAP GTS Consultants
Data Insights Analysts
Oracle Cloud Technical Consultants
Data Modelers
Distribution Consultants
ERP Administrators
SAP MM Consultants
ERP Consultants
Financial Planning Analysts
Intelligence Analysts
SAP Basis Consultants
Consulting Technical Managers
Insights and Reporting Analysts
Advanced Analytics Analysts
Medical Data Analysts
Solution Architects/Principal Consultants
Technical Architects
Data and Analytics Managers
Travel and Expense Analysts
OFSAA Business Analysts
Facilities HVAC Technicians
Data Analytics Analysts
Web Data Analysts
Staff Data Engineers
Program Data Analysts
Associate Consultants
Safety Data Analysts
Controllers
CAD Operators
Security Analysts
Oracle Finance Functional Consultants
Enterprise Architects
Finance Data Analysts
ERP Business Systems Analysts
IT Data Analysts
Business Analysis Interns
Information Services Analysts
Data Solutions Consultants
Enterprise Program Managers
SAP MM Analysts
IT Applications Specialists
Oracle EBS Functional Consultants
Research and Evaluation Analysts
Customer Support Leads
Oracle SCM Functional Consultants
IT Data Analytics Analysts
Finance Assistants
Business Operations Associates
Quality Assurance Automation Testers
Technical Project Managers
Data Science and Analytics Managers
Oracle Cloud HCM Consultants
Business Intelligence Managers
Customer Support Administrators
Procurement Operations Associates
Analytics and Reporting Analysts
Research Specialists
BSA Consultants
Oracle Systems Analysts
Bilingual Office Managers
Institutional Sales Analysts
Consulting Solutions Architects
Corporate Finance Associates
Immunology Specialists
Global Trade Analysts
Search Coordinators
Higher Education Consultants
Enterprise Risk Analysts
ERP Support Specialists
Spanish and English Teachers
Assistant Vice Presidents
Data Protection Specialists
Manufacturing Services Managers
Cash Managers
Pricing Data Analysts
Directors of Toxicology
Data Acquisition Analysts
Process Analysts
Data Technicians
Clinical Quality Analysts
ERP Specialists
IAM Analysts
Data Infrastructure Engineers
Industry Analysts
Oracle Database Developers
Platform Support Specialists
School Education Managers
Clinical Data Associates
Oracle Functional Consultants
Enterprise Analysts
Manufacturing Analysts
Targeting Analysts
Master Data Analysts
SAP Basis Administrators
Data Center Analysts
Business Assistants
Philanthropy Assistants
IT Analysts
Middle Office Analysts
Investment Data Analysts
Salesforce Consultants

#gave chatgpt the list and told it to split into ai impacted and not
ai_impacted_jobs = [
    'Trust Officers',
    'Cloud Migration Engineers',
    'EDI Developers',
    'Permit Specialists',
    'Data Integration Leads',
    'Blockchain Developers',
    'Lead Intelligence Analysts',
    'Sales Architects',
    'Business Intelligence Leads',
    'Data Analyst Managers',
    'Project Support Analysts',
    'Sales Planning Analysts',
    'Hourly Managers',
    'Industrial Equipment Mechanics',
    'Value Engineers',
    'Enterprise Applications Consultants',
    'SAP FICO Consultants',
    'SAP ABAP Consultants',
    'Oracle Erp Consultants',
    'Member Liaisons',
    'Data Solutions Analysts',
    'Transformation Analysts',
    'GIS Data Analysts',
    'Japanese Bilingual Administrative Assistants',
    'Project Leads',
    'People Analytics Analysts',
    'Human Resources Reporting Analysts',
    'Implementation Consultants',
    'Lead Enterprise Architects',
    'Branch Bankers',
    'Growth Marketing Analysts',
    'Chargeback Analysts',
    'Strategy Leads',
    'Innovation Analysts',
    'Business Insights Managers',
    'CRM Business Analysts',
    'Localization Producers',
    'EDI Analysts',
    'Scientific Data Analysts',
    'Bilingual Japanese Customer Service Representatives',
    'SQL/ETL Developers',
    'Data Quality Leads',
    'Data Visualization Analysts',
    'Data Analytics Engineers',
    'Foundation Administrators',
    'SQL Reporting Analysts',
    'Procurement Analysts',
    'Manual Testers',
    'Analytics Associates',
    'Supply Chain Architects',
    'SAP SD Analysts',
    'Oracle Cloud Financials Consultants',
    'Data Quality Assurance Analysts',
    'Client Finance Directors',
    'Population Health Analysts',
    'Enterprise Solutions Consultants',
    'Digital Product Analysts',
    'Line Pilots',
    'Processing Clerks',
    'Client Solutions Strategists',
    'Business Intelligence Specialists',
    'Accounting Consultants',
    'Business Intelligence Analysts',
    'SAP CRM Consultants',
    'Human Capital Management Consultants',
    'IAM Architects',
    'SAP Ariba Consultants',
    'Territory Assistants',
    'Immigration Analysts',
    'Customer Experience Associates',
    'Global Analysts',
    'Analysts',
    'Document Management Clerks',
    'Data Reporting Analysts',
    'Quality Analysts',
    'SAP HANA Consultants',
    'Site Analysts',
    'Business Architects',
    'Data Analytics Developers',
    'Digital Solution Architect Managers',
    'Information Governance Analysts',
    'Business Coaches',
    'Configuration Management Analysts',
    'Commercial Analysts',
    'Analytics Consultants',
    'Buyers',
    'Enterprise Solutions Architects',
    'Remediation Analysts',
    'Search Planners',
    'Insurance Associates',
    'People Operations Generalists',
    'Appeals Specialists',
    'IT Project Assistants',
    'Data Analytics Architects',
    'Methods Analysts',
    'Liquidity Analysts',
    'Functional Consultants',
    'SAP HR Consultants',
    'Performance Directors',
    'ERP Implementation Specialists',
    'Tribal Administrators',
    'Study Design Leads',
    'Equipment Analysts',
    'Quality Assurance Monitors',
    'Integration Team Leads',
    'Invoice Analysts',
    'SAP FICO Functional Consultants',
    'Enterprise Relationship Managers',
    'Enterprise Data Architects',
    'SAP PP/QM Consultants',
    'Management Analysts',
    'Speech Assistants',
    'SAP Business Analysts',
    'Data Migration Analysts',
    'SAP Techno Functional Consultants',
    'SAP Supply Chain Consultants',
    'Enterprise Directors',
    'Data Processing Analysts',
    'SAS Data Analysts',
    'Manufacturing Consultants',
    'Demand Analysts',
    'Netsuite Principal Consultants',
    'OTM Consultants',
    'Modernization Superintendents',
    'Mapping Analysts',
    'Enterprise Systems Architects',
    'Business Intelligence Associates',
    'Oracle Business Analysts',
    'SAP Support Analysts',
    'Automation Engineers',
    'Excel VBA Developers',
    'Financial Data Analysts',
    'Power Analysts',
    'Sales Analysts',
    'Lead IT Analysts',
    'Development Analysts',
    'Analytics Managers',
    'Financial Applications Specialists',
    'Sales Solution Architects',
    'Survey Analysts',
    'Data Analysts/Data Scientists',
    'Quality Control Reviewers',
    'Oracle EBS Consultants',
    'Data Services Analysts',
    'ERP Implementation Managers',
    'Knowledge Analysts',
    'Enterprise Business Analysts',
    'Test Data Analysts',
    'Techno Functional Analysts',
    'Netsuite Consultants',
    'Cryptologists',
    'PMO Analysts',
    'Reference Data Analysts',
    'Clinical Data Analysts',
    'Enterprise Business Architects',
    'Enterprise Cloud Architects',
    'SAP Security Consultants',
    'Data Leads',
    'Tools Developers',
    'Marketing Analysts',
    'Financial Planning and Analysis Analysts',
    'Finance Systems Analysts',
    'Production Operators',
    'Oracle Functional Analysts',
    'IT Buyers',
    'Process Engineers',
    'Privacy Analysts',
    'Enterprise Resources Planning Managers',
    'Real Estate Analysts',
    'Systems Integration Architects',
    'End User Computing Analysts',
    'Data Analysts/Developers',
    'Publishing Specialists',
    'SQL Analysts',
    'Account Analysts',
    'Engineering Data Analysts',
    'Oracle EBS Business Analysts',
    'Directors of Business Intelligence',
    'Reporting Associates',
    'SAP HCM Consultants',
    'Feasibility Managers',
    'Data Management Administrators',
    'Walkers',
    'Production Analysts',
    'HRIS Associates',
    'Data Analytics Leads',
    'Data Analytics Specialists',
    'Data Security Analysts',
    'Principal Data Scientists',
    'Researchers',
    'Procurement Business Analysts',
    'Oracle Applications Analysts',
    'Forecast Analysts',
    'Supply Chain Data Analysts',
    'Analytics and Insights Managers',
    'MDM Developers',
    'Business Support Analysts',
    'Food and Beverage Analysts',
    'Intelligence Research Analysts',
    'Validation Leads',
    'Associate Business Managers',
    'Enterprise Data Analysts',
    'IT Governance Analysts',
    'Domain Architects',
    'Compliance Business Analysts',
    'Implementation Specialists',
    'Placement Managers',
    'Corporate Architects',
    'Splunk Developers',
    'Work Force Management Analysts',
    'Banking Consultants',
    'Data Stewards',
    'SAP MDM Consultants',
    'SAP Specialists',
    'Data Validation Analysts',
    'Business Intelligence Data Warehouse Architects',
    'Data Science Associates',
    'Solution Leads',
    'SAP Data Analysts',
    'SAP Finance Consultants',
    'Doctors',
    'SQL Data Analysts',
    'Patient Revenue Cycle Specialists',
    'People Analytics Managers',
    'Data Scientists',
    'Digital Data Analysts',
    'Data Control Clerks',
    'Storeroom Clerks',
    'Finance Business Analysts',
    'SAP HR Analysts',
    'Business Intelligence and Analytics Managers',
    'Brand Activation Managers',
    'Enterprise Project Managers',
    'Data Analytics Consultants',
    'Programmer Analysts',
    'Sales Data Analysts',
    'Data Reviewers',
    'Contract Analysts',
    'Decision Support Analysts',
    'Data Associates',
    'E-Commerce Architects',
    'Risk Control Managers',
    'Debt Specialists',
    'Risk and Controls Managers',
    'Data Entry Analysts',
    'Platform Analysts',
    'Financial Systems Analysts',
    'Claims Resolution Analysts',
    'Lead Business Intelligence Analysts',
    'Inside Auto Claims Representatives',
    'Customer Contact Center Managers',
    'Data Governance Analysts',
    'Business Operations Specialists',
    'SAP Technical Consultants',
    'Data Engineering Managers',
    'SAP EWM Consultants',
    'Tax Controllers',
    'Transmission Analysts',
    'Business Analysts',
    'Credit Analysts',
    'CSV Consultants',
    'Patient Services Associates',
    'Research Data Analysts',
    'Medical Economics Analysts',
    'SQL Administrators',
    'SAP Master Data Analysts',
    'Customer Care Analysts',
    'Title Processors',
    'Power Business Intelligence Developers',
    'Data Quality Analysts',
    'Quality Assurance Analysts',
    'Consulting Engagement Managers',
    'Vice Presidents of Operational Excellence',
    'Insights Analysts',
    'Inventory Analysts',
    'Data Support Analysts',
    'SAP Business One Consultants',
    'Data Management Leads',
    'Platform Administrators',
    'Oracle Financial Functional Consultants',
    'Architecture Managers',
    'ERP Analysts',
    'Growth Associates',
    'Directors of School Nutrition',
    'Operations and Policy Analysts',
    'Lead Data Analysts',
    'SAP SD Functional Consultants',
    'Assistant Federal Security Directors',
    'Oracle Cloud Architects',
    'Quality Assurance Leads',
    'SAP FICO Analysts',
    'Repair Analysts',
    'SAP SD Consultants',
    'SAP SRM Consultants',
    'Hyperion Consultants',
    'SAP Systems Analysts',
    'Adobe Experience Manager Architects',
    'Quality Assurance Analysts/Engineers',
    'Oracle Utilities Professional Services CC&B Implementation Consultants',
    'SAP OTC Consultants',
    'Data Warehouse Business Analysts',
    'Client Data Analysts',
    'Documentum Administrators',
    'Project Analysts',
    'Korean Linguists',
    'Data Governance Managers',
    'Oracle Consultants',
    'Navigators',
    'Customer Data Analysts',
    'Enterprise Technical Architects',
    'Cash Management Associates',
    'Equity Traders',
    'Financial Analysts',
    'ERP Architects',
    'Business Intelligence and Data Analysts',
    'Automation Consultants',
    'Governance Analysts',
    'ERP Systems Analysts',
    'Commercial Banking Credit Analysts',
    'Certified Public Accountants',
    'Data Operations Analysts',
    'Category Analysts',
    'Regional Business Managers',
    'GCP Auditors',
    'Architectural Project Coordinators',
    'Delivery Analysts',
    'SAP MM Functional Consultants',
    'Techno Functional Leads',
    'Clinical Data Managers',
    'Administrators',
    'Enterprise Network Managers',
    'Healthcare Business Analysts',
    'Solutions Architects',
    'Ward Clerks',
    'Account Coordinators',
    'Data Abstractors',
    'Data Scientists/Analysts',
    'SAP Functional Consultants',
    'Product Lifecycle Managers',
    'Data Intelligence Analysts',
    'Data Warehouse Analysts',
    'CNC Administrators',
    'Life Sciences Consultants',
    'Data Processing Clerks',
    'Production Planning Analysts',
    'Business Growth Consultants',
    'Chief Architects',
    'Logistics Administrators',
    'Vendor Analysts',
    'Quantitative Analytics Managers',
    'Treasury Consultants',
    'Crime Analysts',
    'SAP Business Process Analysts',
    'Call Center Analysts',
    'GRC Analysts',
    'Competitive Intelligence Analysts',
    'Oracle Technical Consultants',
    'Headend Engineers',
    'Claims Business Analysts',
    'General Managers',
    'Systems Architecture Engineers',
    'SAP Ariba Managers',
    'Customer Analytics Managers',
    'Customer Experience Analysts',
    'LIS Analysts',
    'SAP Consultants',
    'SAP Hybris Consultants',
    'SAP PI Consultants',
    'Enterprise Application Analysts',
    'Data and Analytics Consultants',
    'Agile Product Owners',
    'Business Intelligence Reporting Analysts',
    'Management Associates',
    'Health Data Analysts',
    'Enterprise Systems Engineers',
    'Lead Analysts',
    'Data Migration Consultants',
    'Professors of Biological Sciences',
    'Oracle Business Systems Analysts',
    'Regional Fleet Managers',
    'Enterprise Systems Analysts',
    'Inflight Supervisors',
    'Operations Process Engineers',
    'SAP Developers',
    'Customer Care Experts',
    'Reporting Analysts',
    'Commercial Excellence Managers',
    'Lead Quality Analysts',
    'Oracle EBS Technical Consultants',
    'Records Assistants',
    'Directors of Cloud Security',
    '.NET Technical Architects',
    'Associates',
    'Trend Analysts',
    'Digital Analysts',
    'Innovation Specialists',
    'Data Governance Leads',
    'Data Analysts',
    'Directors of Sponsored Programs',
    'Enterprise Resource Planning Managers',
    'Wealth Management Analysts',
    'Quantitative Data Analysts',
    'Origination Associates',
    'Enterprise Business Consultants',
    'SAP Application Consultants',
    'Information Analysts',
    'Process Improvement Analysts',
    'Consumer Analysts',
    'Communications Analysts',
    'GIS Consultants',
    'Data Analyst Specialists',
    'Plant Maintenance Managers',
    'Netsuite Functional Consultants',
    'Plant Chemists',
    'Integration Consultants',
    'Data Management Analysts',
    'Oracle Financial Consultants',
    'SAP Plant Maintenance Consultants',
    'Analytics Leads',
    'Lead Level Designers',
    'Solutions Analysts',
    'Data and Reporting Analysts',
    'Data Analytics Product Managers',
    'SAP Analysts',
    'Implementation Analysts',
    'Revenue Cycle Analysts',
    'Case Management Associates',
    'SAP Successfactors Consultants',
    'IT Governance Managers',
    'Sales Professionals',
    'Principal Architects',
    'Oracle HCM Consultants',
    'Data Science Analysts',
    'HANA Consultants',
    'OSP Managers',
    'Interface Analysts',
    'Transportation Systems Analysts',
    'Forms Analysts',
    'Master Data Coordinators',
    'Enterprise Application Architects',
    'Epic Security Analysts',
    'Intercompany Accountants',
    'Client Insights Analysts',
    'Data Analytics Associates',
    'SAP SD/MM Consultants',
    'SAP Administrators',
    'Supply Chain Business Managers',
    'Assistant Service Experience Managers',
    'Data Clerks',
    'Data Visualization Specialists',
    'Growth Specialists',
    'SAP Basis Leads',
    'Functional Analysts',
    'SAP Functional Analysts',
    'Knowledge Experts',
    'Artificial Intelligence Engineers',
    'Financial Planning and Analysis Managers',
    'Business Intelligence Data Analysts',
    'Peoplesoft Business Analysts',
    'Platform Architects',
    'Systems Architects',
    'Assessment Analysts',
    'Solutions Consultants',
    'Surface Designers',
    'SAP GTS Consultants',
    'Data Insights Analysts',
    'Oracle Cloud Technical Consultants',
    'Data Modelers',
    'Distribution Consultants',
    'ERP Administrators',
    'SAP MM Consultants',
    'ERP Consultants',
    'Financial Planning Analysts',
    'Intelligence Analysts',
    'SAP Basis Consultants',
    'Consulting Technical Managers',
    'Insights and Reporting Analysts',
    'Advanced Analytics Analysts',
    'Medical Data Analysts',
    'Solution Architects/Principal Consultants',
    'Technical Architects',
    'Data and Analytics Managers',
    'Travel and Expense Analysts',
    'OFSAA Business Analysts',
    'Facilities HVAC Technicians',
    'Data Analytics Analysts',
    'Web Data Analysts',
    'Staff Data Engineers',
    'Program Data Analysts',
    'Associate Consultants',
    'Safety Data Analysts',
    'Controllers',
    'CAD Operators',
    'Security Analysts',
    'Oracle Finance Functional Consultants',
    'Enterprise Architects',
    'Finance Data Analysts',
    'ERP Business Systems Analysts',
    'IT Data Analysts',
    'Business Analysis Interns',
    'Information Services Analysts',
    'Data Solutions Consultants',
    'Enterprise Program Managers',
    'SAP MM Analysts',
    'IT Applications Specialists',
    'Oracle EBS Functional Consultants',
    'Research and Evaluation Analysts',
    'Customer Support Leads',
    'Oracle SCM Functional Consultants',
    'IT Data Analytics Analysts',
    'Finance Assistants',
    'Business Operations Associates',
    'Quality Assurance Automation Testers',
    'Technical Project Managers',
    'Data Science and Analytics Managers',
    'Oracle Cloud HCM Consultants',
    'Business Intelligence Managers',
    'Customer Support Administrators',
    'Procurement Operations Associates',
    'Analytics and Reporting Analysts',
    'Research Specialists',
    'BSA Consultants',
    'Oracle Systems Analysts',
    'Bilingual Office Managers',
    'Institutional Sales Analysts',
    'Consulting Solutions Architects',
    'Corporate Finance Associates',
    'Immunology Specialists',
    'Global Trade Analysts',
    'Search Coordinators',
    'Higher Education Consultants',
    'Enterprise Risk Analysts',
    'ERP Support Specialists',
    'Spanish and English Teachers',
    'Assistant Vice Presidents',
    'Data Protection Specialists',
    'Manufacturing Services Managers',
    'Cash Managers',
    'Pricing Data Analysts',
    'Directors of Toxicology',
    'Data Acquisition Analysts',
    'Process Analysts',
    'Data Technicians',
    'Clinical Quality Analysts',
    'ERP Specialists',
    'IAM Analysts',
    'Data Infrastructure Engineers',
    'Industry Analysts',
    'Oracle Database Developers',
    'Platform Support Specialists',
    'School Education Managers',
    'Clinical Data Associates',
    'Oracle Functional Consultants',
    'Enterprise Analysts',
    'Manufacturing Analysts',
    'Targeting Analysts',
    'Master Data Analysts',
    'SAP Basis Administrators',
    'Data Center Analysts',
    'Business Assistants',
    'Philanthropy Assistants',
    'IT Analysts',
    'Middle Office Analysts',
    'Investment Data Analysts',
    'Salesforce Consultants'
]

from pyspark.sql.functions import when, col, lit, avg, count

df_final = df_clean.withColumn(
    'ai_impacted',
    when(col('title_name').isin(ai_impacted_jobs), lit('yes')).otherwise(lit('no'))
)

avg_salary = df_final.groupBy("state_name").agg(avg("salary").alias("avg_salary"))


df_final = df_final.join(avg_salary, on="state_name", how="left")

count = df_final.groupBy("state_name").agg(count("*").alias("count"))


df_final = df_final.join(count, on="state_name", how="left")

df_final.select("count").distinct().show()

[Stage 7:>                                                          (0 + 1) / 1][Stage 7:>                  (0 + 1) / 1][Stage 8:>                  (0 + 1) / 1][Stage 8:>                                                          (0 + 1) / 1]                                                                                

+-----+
|count|
+-----+
|   26|
|   29|
|   19|
|   77|
|   34|
|   50|
|   57|
|   31|
|   68|
|    9|
|   27|
|   63|
|   56|
|   17|
|  248|
|   10|
|   48|
|   67|
|  108|
|   37|
+-----+
only showing top 20 rows

df_final_pd = df_final.toPandas()

fig = px.scatter(
    df_final_pd,  
    x="STATE_NAME",                       
    y="avg_salary",                 
    size="count",                 
    color="ai_impacted",                  
    title="Average Salary by State and AI Impact",
    size_max=60,
    color_discrete_map={
        "yes": "#2dbf78",   
        "no": "#8adfbd"  
    }

)

fig.update_layout(
    legend_title_text="AI Impacted",
    xaxis_title="State",
    yaxis_title="Average Salary",
    xaxis=dict(tickangle=45),
    yaxis=dict(categoryorder='category descending')
)

fig.show()

[Stage 16:>                                                         (0 + 1) / 1][Stage 16:>                 (0 + 1) / 1][Stage 17:>                 (0 + 1) / 1][Stage 16:>   (0 + 1) / 1][Stage 17:>   (0 + 1) / 1][Stage 18:>   (0 + 0) / 1][Stage 16:>                 (0 + 1) / 1][Stage 18:>                 (0 + 1) / 1][Stage 16:>   (0 + 1) / 1][Stage 18:>   (0 + 1) / 1][Stage 20:>   (0 + 0) / 1][Stage 18:>                 (0 + 1) / 1][Stage 20:>                 (0 + 1) / 1]                                                                                [Stage 18:>                                                         (0 + 1) / 1]                                                                                [Stage 24:>                                                         (0 + 2) / 2]